
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 12/16/2011 18:19:59
-- Generated from EDMX file: D:\Library\Documents\Visual Studio 2010\Projects\fantasy-blog-asp\FantasyBlog.DAL\BlogModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [BlogDB];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_UserPost]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PostSet] DROP CONSTRAINT [FK_UserPost];
GO
IF OBJECT_ID(N'[dbo].[FK_PostComment]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CommentSet] DROP CONSTRAINT [FK_PostComment];
GO
IF OBJECT_ID(N'[dbo].[FK_UserComment]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CommentSet] DROP CONSTRAINT [FK_UserComment];
GO
IF OBJECT_ID(N'[dbo].[FK_TermPost_Term]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TermPost] DROP CONSTRAINT [FK_TermPost_Term];
GO
IF OBJECT_ID(N'[dbo].[FK_TermPost_Post]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TermPost] DROP CONSTRAINT [FK_TermPost_Post];
GO
IF OBJECT_ID(N'[dbo].[FK_CommentComment]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CommentSet] DROP CONSTRAINT [FK_CommentComment];
GO
IF OBJECT_ID(N'[dbo].[FK_PostCategory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PostSet] DROP CONSTRAINT [FK_PostCategory];
GO
IF OBJECT_ID(N'[dbo].[FK_CategoryCategory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CategorySet] DROP CONSTRAINT [FK_CategoryCategory];
GO
IF OBJECT_ID(N'[dbo].[FK_UserRole_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserRole] DROP CONSTRAINT [FK_UserRole_User];
GO
IF OBJECT_ID(N'[dbo].[FK_UserRole_Role]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserRole] DROP CONSTRAINT [FK_UserRole_Role];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[UserSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserSet];
GO
IF OBJECT_ID(N'[dbo].[PostSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PostSet];
GO
IF OBJECT_ID(N'[dbo].[CommentSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CommentSet];
GO
IF OBJECT_ID(N'[dbo].[TermSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TermSet];
GO
IF OBJECT_ID(N'[dbo].[CategorySet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CategorySet];
GO
IF OBJECT_ID(N'[dbo].[RoleSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RoleSet];
GO
IF OBJECT_ID(N'[dbo].[SiteMetadataSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[SiteMetadataSet];
GO
IF OBJECT_ID(N'[dbo].[TermPost]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TermPost];
GO
IF OBJECT_ID(N'[dbo].[UserRole]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserRole];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'UserSet'
CREATE TABLE [dbo].[UserSet] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [username] nvarchar(max)  NOT NULL,
    [password] nvarchar(max)  NOT NULL,
    [displayname] nvarchar(max)  NOT NULL,
    [email] nvarchar(max)  NULL,
    [registered] datetime  NOT NULL,
    [status] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'PostSet'
CREATE TABLE [dbo].[PostSet] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [date] datetime  NOT NULL,
    [title] nvarchar(max)  NOT NULL,
    [excerpt] nvarchar(max)  NULL,
    [status] nvarchar(max)  NOT NULL,
    [comment_status] nvarchar(max)  NOT NULL,
    [modified_time] datetime  NOT NULL,
    [content] nvarchar(max)  NOT NULL,
    [comment_count] int  NOT NULL,
    [PostUser_ID] int  NOT NULL,
    [Category_ID] int  NULL
);
GO

-- Creating table 'CommentSet'
CREATE TABLE [dbo].[CommentSet] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [author] nvarchar(max)  NOT NULL,
    [author_email] nvarchar(max)  NOT NULL,
    [date] datetime  NOT NULL,
    [content] nvarchar(max)  NOT NULL,
    [ip] nvarchar(max)  NULL,
    [comment_approved] nvarchar(max)  NOT NULL,
    [Post_ID] int  NOT NULL,
    [User_ID] int  NULL,
    [Parent_ID] int  NULL
);
GO

-- Creating table 'TermSet'
CREATE TABLE [dbo].[TermSet] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [count] nvarchar(max)  NOT NULL,
    [name] nvarchar(max)  NOT NULL,
    [slug] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'CategorySet'
CREATE TABLE [dbo].[CategorySet] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [name] nvarchar(max)  NOT NULL,
    [Parent_ID] int  NULL
);
GO

-- Creating table 'RoleSet'
CREATE TABLE [dbo].[RoleSet] (
    [name] nchar(50)  NOT NULL,
    [description] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'SiteMetadataSet'
CREATE TABLE [dbo].[SiteMetadataSet] (
    [Key] nchar(50)  NOT NULL,
    [Value] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'TermPost'
CREATE TABLE [dbo].[TermPost] (
    [Terms_ID] int  NOT NULL,
    [Posts_ID] int  NOT NULL
);
GO

-- Creating table 'UserRole'
CREATE TABLE [dbo].[UserRole] (
    [Users_ID] int  NOT NULL,
    [Roles_name] nchar(50)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'UserSet'
ALTER TABLE [dbo].[UserSet]
ADD CONSTRAINT [PK_UserSet]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'PostSet'
ALTER TABLE [dbo].[PostSet]
ADD CONSTRAINT [PK_PostSet]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'CommentSet'
ALTER TABLE [dbo].[CommentSet]
ADD CONSTRAINT [PK_CommentSet]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'TermSet'
ALTER TABLE [dbo].[TermSet]
ADD CONSTRAINT [PK_TermSet]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'CategorySet'
ALTER TABLE [dbo].[CategorySet]
ADD CONSTRAINT [PK_CategorySet]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [name] in table 'RoleSet'
ALTER TABLE [dbo].[RoleSet]
ADD CONSTRAINT [PK_RoleSet]
    PRIMARY KEY CLUSTERED ([name] ASC);
GO

-- Creating primary key on [Key] in table 'SiteMetadataSet'
ALTER TABLE [dbo].[SiteMetadataSet]
ADD CONSTRAINT [PK_SiteMetadataSet]
    PRIMARY KEY CLUSTERED ([Key] ASC);
GO

-- Creating primary key on [Terms_ID], [Posts_ID] in table 'TermPost'
ALTER TABLE [dbo].[TermPost]
ADD CONSTRAINT [PK_TermPost]
    PRIMARY KEY NONCLUSTERED ([Terms_ID], [Posts_ID] ASC);
GO

-- Creating primary key on [Users_ID], [Roles_name] in table 'UserRole'
ALTER TABLE [dbo].[UserRole]
ADD CONSTRAINT [PK_UserRole]
    PRIMARY KEY NONCLUSTERED ([Users_ID], [Roles_name] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [PostUser_ID] in table 'PostSet'
ALTER TABLE [dbo].[PostSet]
ADD CONSTRAINT [FK_UserPost]
    FOREIGN KEY ([PostUser_ID])
    REFERENCES [dbo].[UserSet]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserPost'
CREATE INDEX [IX_FK_UserPost]
ON [dbo].[PostSet]
    ([PostUser_ID]);
GO

-- Creating foreign key on [Post_ID] in table 'CommentSet'
ALTER TABLE [dbo].[CommentSet]
ADD CONSTRAINT [FK_PostComment]
    FOREIGN KEY ([Post_ID])
    REFERENCES [dbo].[PostSet]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PostComment'
CREATE INDEX [IX_FK_PostComment]
ON [dbo].[CommentSet]
    ([Post_ID]);
GO

-- Creating foreign key on [User_ID] in table 'CommentSet'
ALTER TABLE [dbo].[CommentSet]
ADD CONSTRAINT [FK_UserComment]
    FOREIGN KEY ([User_ID])
    REFERENCES [dbo].[UserSet]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserComment'
CREATE INDEX [IX_FK_UserComment]
ON [dbo].[CommentSet]
    ([User_ID]);
GO

-- Creating foreign key on [Terms_ID] in table 'TermPost'
ALTER TABLE [dbo].[TermPost]
ADD CONSTRAINT [FK_TermPost_Term]
    FOREIGN KEY ([Terms_ID])
    REFERENCES [dbo].[TermSet]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Posts_ID] in table 'TermPost'
ALTER TABLE [dbo].[TermPost]
ADD CONSTRAINT [FK_TermPost_Post]
    FOREIGN KEY ([Posts_ID])
    REFERENCES [dbo].[PostSet]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TermPost_Post'
CREATE INDEX [IX_FK_TermPost_Post]
ON [dbo].[TermPost]
    ([Posts_ID]);
GO

-- Creating foreign key on [Parent_ID] in table 'CommentSet'
ALTER TABLE [dbo].[CommentSet]
ADD CONSTRAINT [FK_CommentComment]
    FOREIGN KEY ([Parent_ID])
    REFERENCES [dbo].[CommentSet]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CommentComment'
CREATE INDEX [IX_FK_CommentComment]
ON [dbo].[CommentSet]
    ([Parent_ID]);
GO

-- Creating foreign key on [Category_ID] in table 'PostSet'
ALTER TABLE [dbo].[PostSet]
ADD CONSTRAINT [FK_PostCategory]
    FOREIGN KEY ([Category_ID])
    REFERENCES [dbo].[CategorySet]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PostCategory'
CREATE INDEX [IX_FK_PostCategory]
ON [dbo].[PostSet]
    ([Category_ID]);
GO

-- Creating foreign key on [Parent_ID] in table 'CategorySet'
ALTER TABLE [dbo].[CategorySet]
ADD CONSTRAINT [FK_CategoryCategory]
    FOREIGN KEY ([Parent_ID])
    REFERENCES [dbo].[CategorySet]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CategoryCategory'
CREATE INDEX [IX_FK_CategoryCategory]
ON [dbo].[CategorySet]
    ([Parent_ID]);
GO

-- Creating foreign key on [Users_ID] in table 'UserRole'
ALTER TABLE [dbo].[UserRole]
ADD CONSTRAINT [FK_UserRole_User]
    FOREIGN KEY ([Users_ID])
    REFERENCES [dbo].[UserSet]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Roles_name] in table 'UserRole'
ALTER TABLE [dbo].[UserRole]
ADD CONSTRAINT [FK_UserRole_Role]
    FOREIGN KEY ([Roles_name])
    REFERENCES [dbo].[RoleSet]
        ([name])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserRole_Role'
CREATE INDEX [IX_FK_UserRole_Role]
ON [dbo].[UserRole]
    ([Roles_name]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------